package com.cuit.freshmanMS.service.impl;

import com.baomidou.mybatisplus.core.toolkit.StringUtils;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.cuit.freshmanMS.domain.Student;
import com.cuit.freshmanMS.mapper.ExcelMapper;
import com.cuit.freshmanMS.mapper.StudentMapper;
import com.cuit.freshmanMS.service.ExcelService;
import com.cuit.freshmanMS.util.ExcelTool;
import com.cuit.freshmanMS.util.result.CommonResult;
import com.cuit.freshmanMS.util.result.ResultCode;
import com.cuit.freshmanMS.util.result.ResultUtil;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import java.io.*;
import java.text.NumberFormat;
import java.util.ArrayList;
import java.util.List;

@Service
public class ExcelServiceImpl extends ServiceImpl<ExcelMapper, Student> implements ExcelService {

    @Resource
    private ExcelMapper excelMapper;
    @Resource
    private StudentMapper studentMapper;
    List<Student> studentList;

    /**
     * 导入学生文件
     *
     * @param file
     * @return
     */
    @Override
    public CommonResult<Boolean> importFile(MultipartFile file) throws IOException {
        //文件类型是否正确
        String postfix = ExcelTool.getPostfix(file.getOriginalFilename());
        if (!"xlsx".equals(postfix) && !"xls".equals(postfix)) {
            return ResultUtil.fail(ResultCode.FORMAT_ERROR);
        }
        //清空studentList
        studentList = new ArrayList<>();

        // 读取如果读取失败则直接返回错误信息
        CommonResult<Boolean> readResult = readDataFromExcel(file, 0, 1);
        if (!readResult.getSucceed()) {
            return readResult;
        }

        // 解析Excel数据  写入studentList
        if (studentList == null || studentList.size() <= 0) {
            return ResultUtil.fail(ResultCode.PARAM_IS_BLANK);
        }
        // 插入数据表格中的数据
        this.saveBatch(studentList);

        return ResultUtil.success();
    }

    /**
     * 导出文件
     *
     * @return
     * @throws IOException
     */
    @Override
    public CommonResult<Boolean> exportFile() throws IOException {
        List<Student> studentList = excelMapper.getAllStudentFromDB();
        return export(studentList);
    }

    /**
     * 选择sids导出
     * @param sids
     * @return
     * @throws IOException
     */
    @Override
    public CommonResult<Boolean> selectExportFile(List<String> sids) throws IOException {
        List<Student> studentList = studentMapper.selectBatchIds(sids);
        return export(studentList);
    }

    /**
     * 导出文件核心处理
     * @param studentList
     * @return
     * @throws IOException
     */
    public CommonResult<Boolean> export(List<Student> studentList) throws IOException {
        //需要：将查询出的所有数据库数据写入xlxs文件
        //先打开文件，这里是存的项目静态文件
        String filePath = "src/main/resources/templates/template.xlsx"; // 指定文件路径
        FileInputStream fis = new FileInputStream(filePath);
        Workbook workbook = WorkbookFactory.create(fis);
        fis.close();
        Sheet sheet = workbook.getSheetAt(0); // 只有一个表

        //设置一下表格行样式
        //默认单元格宽度
        sheet.setDefaultColumnWidth(30);
        // 设置行高为18磅
        int rowHeightInPoints = 18;
        short rowHeight = (short) (rowHeightInPoints * 20);
        sheet.setDefaultRowHeight(rowHeight);

        //第一行设置粗体，居中
        CellStyle boldStyle = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setBold(true);
        boldStyle.setFont(font);
        boldStyle.setAlignment(HorizontalAlignment.CENTER);
        boldStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        int lastRowNum = sheet.getLastRowNum();

        Row row1 = sheet.getRow(0); // 第一行

        Cell idNumber = row1.getCell(0);
        idNumber.setCellStyle(boldStyle);
        idNumber.setCellValue("身份证号");

        Cell name = row1.getCell(1); // 获取特定单元格对象
        name.setCellStyle(boldStyle);
        name.setCellValue("姓名");

        Cell no = row1.getCell(2); // 获取特定单元格对象
        no.setCellStyle(boldStyle);
        no.setCellValue("学号");

        Cell sex = row1.getCell(3); // 获取特定单元格对象
        sex.setCellStyle(boldStyle);
        sex.setCellValue("性别");

        Cell tel = row1.getCell(4); // 获取特定单元格对象
        tel.setCellStyle(boldStyle);
        tel.setCellValue("电话");

        Cell address = row1.getCell(5); // 获取特定单元格对象
        address.setCellStyle(boldStyle);
        address.setCellValue("地址");

        Cell dno = row1.getCell(6); // 获取特定单元格对象
        dno.setCellStyle(boldStyle);
        dno.setCellValue("寝室号");

        Cell cname = row1.getCell(7); // 获取特定单元格对象
        cname.setCellStyle(boldStyle);
        cname.setCellValue("班级");

        Cell aname = row1.getCell(8); // 获取特定单元格对象
        aname.setCellStyle(boldStyle);
        aname.setCellValue("学院");

        // 单元格居中
        CellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);

        //修改单元格的值
        for (int i = 1; i <= studentList.size(); i++) {
            //i是行数 从1开始 因为表格第一行是元数据  获取对象就要从i-1 从数据库第一行开始获取
            Row row = sheet.getRow(i); // 获取特定行对象
            if (row == null) {
                row = sheet.createRow(i);// 数据增加之后，现在的数据比项目中静态文件数据少，就有为空的行，则创建新的行
            }

            Cell cell0 = row.getCell(0); // 获取特定单元格对象
            if (cell0 == null) {
                cell0 = row.createCell(0); // 若该单元格不存在则创建新的单元格
            }
            cell0.setCellStyle(style);
            cell0.setCellValue(studentList.get(i-1).getSid());

            Cell cell1 = row.getCell(1); // 获取特定单元格对象
            if (cell1 == null) {
                cell1 = row.createCell(1); // 若该单元格不存在则创建新的单元格
            }
            cell1.setCellStyle(style);
            cell1.setCellValue(studentList.get(i-1).getSname());

            Cell cell2 = row.getCell(2); // 获取特定单元格对象
            if (cell2 == null) {
                cell2 = row.createCell(2); // 若该单元格不存在则创建新的单元格
            }
            cell2.setCellStyle(style);
            cell2.setCellValue(studentList.get(i-1).getSno());

            /* //班级号 cno 在student属性中有，但是在studentVO中没有 跳过此属性
            Cell cell3 = row.getCell(3); // 获取特定单元格对象
            if (cell3 == null) {
                cell3 = row.createCell(3); // 若该单元格不存在则创建新的单元格
            }
            cell3.setCellStyle(style);
            cell3.setCellValue(studentList.get(i-1).getCno());
            */

            Cell cell4 = row.getCell(3); // 获取特定单元格对象
            if (cell4 == null) {
                cell4 = row.createCell(3); // 若该单元格不存在则创建新的单元格
            }
            cell4.setCellStyle(style);
            cell4.setCellValue(studentList.get(i-1).getSex());

            Cell cell5 = row.getCell(4); // 获取特定单元格对象
            if (cell5 == null) {
                cell5 = row.createCell(4); // 若该单元格不存在则创建新的单元格
            }
            cell5.setCellStyle(style);
            cell5.setCellValue(studentList.get(i-1).getStel());

            Cell cell6 = row.getCell(5); // 获取特定单元格对象
            if (cell6 == null) {
                cell6 = row.createCell(5); // 若该单元格不存在则创建新的单元格
            }
            cell6.setCellStyle(style);
            cell6.setCellValue(studentList.get(i-1).getAddress());

            Cell cell7 = row.getCell(6); // 获取特定单元格对象
            if (cell7 == null) {
                cell7 = row.createCell(6); // 若该单元格不存在则创建新的单元格
            }
            cell7.setCellStyle(style);
            cell7.setCellValue(studentList.get(i-1).getDno());

            //班级名称
            Cell cell8 = row.getCell(7); // 获取特定单元格对象
            if (cell8 == null) {
                cell8 = row.createCell(7); // 若该单元格不存在则创建新的单元格
            }
            cell8.setCellStyle(style);
            cell8.setCellValue(excelMapper.getCnameByCno(studentList.get(i-1).getCno()));

            //学院名称
            Cell cell9 = row.getCell(8); // 获取特定单元格对象
            if (cell9== null) {
                cell9 = row.createCell(8); // 若该单元格不存在则创建新的单元格
            }
            cell9.setCellStyle(style);
            cell9.setCellValue(excelMapper.getAnameByAno(excelMapper.getAnoByCno(studentList.get(i-1).getCno())));
        }

        // 清除多余数据
        lastRowNum = sheet.getLastRowNum();
        if (lastRowNum > studentList.size()){
            for (int i = studentList.size()+1; i <= lastRowNum; i++) {
                Row row = sheet.getRow(i);
                if (row != null) {
                    sheet.removeRow(row);
                }
            }
        }

        // 将更改写入原来的文件  覆盖
        workbook.write(new FileOutputStream(filePath));
        workbook.close();

        return ResultUtil.success();
    }

    /**
     * 解析Excel数据  返回一个学生的list
     *
     * @param file
     * @param sheetNum
     * @param startRow
     * @return
     */
    public CommonResult<Boolean> readDataFromExcel(MultipartFile file, int sheetNum, int startRow){
        InputStream is = null;
        Workbook workbook = null;
        try {
            is = file.getInputStream();
            workbook = new XSSFWorkbook(is);
        }catch (IOException e) {
            e.printStackTrace();
        }finally {
            if (is != null) {
                try {
                    is.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        if (workbook == null) {
             return ResultUtil.fail(ResultCode.PARAM_IS_BLANK);
        }

        // 判断有记录的列数（表格的列数和表的列一样） 表格使用studentVO格式 就有9列
        if (workbook.getSheetAt(sheetNum).getRow(0).getPhysicalNumberOfCells() !=9) {
             return ResultUtil.fail(ResultCode.FORMAT_ERROR);
        }

        studentList = new ArrayList<>();

        //获取数据库里的身份证号和学号 保持唯一性
        List<Integer> sids = excelMapper.getAllSidFromDB();
        List<Integer> snos = excelMapper.getAllSnoFromDB();

        // 获取表格sheet的内容
        Sheet sheetAt = workbook.getSheetAt(sheetNum);
        // 获得sheet总行数
        int lastRowNum = sheetAt.getLastRowNum();
        if (lastRowNum < 1) {
            return ResultUtil.fail(ResultCode.PARAM_NOT_COMPLETE);
        }

        //判断表头顺序
        Row row1 = sheetAt.getRow(0);
        if (row1 == null) return ResultUtil.fail(ResultCode.PARAM_IS_BLANK);
        Cell cell0 = row1.getCell(0);
        Cell cell1 = row1.getCell(1);
        Cell cell2 = row1.getCell(2);
        Cell cell3 = row1.getCell(3);
        Cell cell4 = row1.getCell(4);
        Cell cell5 = row1.getCell(5);
        Cell cell6 = row1.getCell(6);
        Cell cell7 = row1.getCell(7);
        Cell cell8 = row1.getCell(8);

        if(!(cell0.getStringCellValue().equals("身份证号")  && cell1.getStringCellValue().equals("姓名") && cell2.getStringCellValue().equals("学号")
                && cell3.getStringCellValue().equals("性别") && cell4.getStringCellValue().equals("电话") && cell5.getStringCellValue().equals("地址")
                && cell6.getStringCellValue().equals("寝室号") && cell7.getStringCellValue().equals("班级") && cell8.getStringCellValue().equals("学院"))) {
            return ResultUtil.fail(ResultCode.FORMAT_ERROR);
        }

        // 开始读取,不读取表头所以从第二行开始
        for (int i = startRow; i <= lastRowNum; i++) {
            // 获取每一行
            Row row = sheetAt.getRow(i);
            // 行为空不读取
            if (row == null) continue;

            Cell cell = row.getCell(0);

            // 列为空不读取
            if (cell == null || StringUtils.isEmpty(convertData(cell))) continue;

            // 创建对象封装行数据
            Student student = new Student();

            // 创建一个集合根据下标来确定每个单元格对应对象的什么属性
            List<String> rowList = new ArrayList<>();
            // 添加数据
            for (int j = 0; j < 9; j++) {
                Cell cellOne = row.getCell(j);
                if (cellOne == null) {
                    rowList.add("");
                } else {
                    try {
                        String item = convertData(cellOne);
                        rowList.add(item);
                    } catch (Exception e) {
                        rowList.add("");
                    }
                }
            }
            // 规避行数数据后几行为空
            if (rowList.size() < 9) {
                for (int k = 0; k < 9 - rowList.size(); k++) {
                    rowList.add("");
                }
            }

            // 添加数据
            //转化过来的字符串数据超过三位，中间带有,分隔 要去掉后才能转换成正常的Integer类型
            Integer sid = Integer.valueOf(deleteString(rowList.get(0).trim()));
            //如果身份证号重复了 跳过此人
            if(sids.contains(sid)){
                 return ResultUtil.fail(ResultCode.PARAM_NOT_VALID);
            }
            //将新的人的身份账号加入列表
            sids.add(sid);
            student.setSid(sid);

            //学号同理
            Integer sno = Integer.valueOf(deleteString(rowList.get(2).trim()));
            if(snos.contains(sno)){
                return ResultUtil.fail(ResultCode.PARAM_NOT_VALID);
            }
            snos.add(sno);
            student.setSno(sno);

            //根据班级名称、学院名称 获取他们的班级号、学号 用作比较叛正误
//            Integer cnoByCname = excelMapper.getCnoByCname(rowList.get(7).trim());
//            Integer anoByCname = excelMapper.getAnoByCname(rowList.get(7).trim());
//            Integer anoByAname = excelMapper.getAnoByAname(rowList.get(8).trim());
//            Integer anoByCno = excelMapper.getAnoByCno(cnoByCname);
            Boolean existClassInAca = excelMapper.existClassInAca(rowList.get(7).trim(), rowList.get(8).trim());
            if (!existClassInAca) {
                return ResultUtil.fail(ResultCode.NO_CLASS);
            }
            student.setSname(rowList.get(1).trim());
            student.setCno(excelMapper.getCnoByCname(rowList.get(7).trim()));
            student.setSex(rowList.get(3).trim());
            student.setStel(rowList.get(4).trim());
            student.setAddress(rowList.get(5).trim());
            student.setDno(Integer.valueOf(deleteString(rowList.get(6).trim())));
            studentList.add(student);
        }
        return ResultUtil.success();
    }

    /**
     * 表格数据转换
     *
     * @param cell
     * @return
     */
    public String convertData(Cell cell) {
        String str = "";

        switch (cell.getCellTypeEnum()) {
            case NUMERIC:
                // 判断是否是整数
                str = NumberFormat.getNumberInstance().format(cell.getNumericCellValue());
                break;
            case STRING:
                str = cell.getStringCellValue();
                break;
            case _NONE:
                str = "";
                break;
            case BLANK:
                str = "";
                break;
            case FORMULA:
                try {
                    str = String.valueOf(cell.getNumericCellValue());
                } catch (IllegalArgumentException e) {
                    str = String.valueOf(cell.getRichStringCellValue());
                }
                break;
            default:
                str = "";
        }
        return str;
    }

    /**
     * 删除需要转化为Integer的字符串中的","
     * @param s
     * @return
     */
    public String deleteString(String s){
        return s.replace(",", "");
    }

}
